Imports System.Data
Imports System.Data.SqlClient
Public Class oOrder
    Public Shared Function LoadAllMenu() As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("MENU_LOADBYSTORE", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure

                MyCommand.Parameters.AddWithValue("@STORE_CODE", PublicVariable.POSStoreCode)
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    
    Public Shared Function LoadAllMenuDetail() As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("MENUDETAIL_LOADALL", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
    Public Shared Function BillAdd(ByVal CUST_CODE As String, _
                                    ByVal BILL_DATE As Date, _
                                    ByVal BILL_AMT As Double, _
                                    ByVal BILL_TAX_AMT As Double, _
                                    ByVal BILL_DCPER As Double, _
                                    ByVal BILL_DCAMT As Double, _
                                    ByVal BILL_SVC As Double, _
                                    ByVal BILL_TOTAL As Double, _
                                    ByVal BILL_EMPCODE As String, _
                                    ByVal BILL_ORDERTYPE As Integer, _
                                    ByVal ATTRIBUTE As String, _
                                    ByVal CREATEDUSER As String, _
                                    ByVal TBL_CODE As String, _
                                    ByVal BILL_TIP As Double, _
                                    ByVal PRO_CODE As String, _
                                    ByVal BILL_GUEST As Integer, _
                                    ByVal BILL_NOTES As String, _
                                    ByVal BILL_DEPOSIT As Double) As Int64
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_Add", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@CUST_CODE", CUST_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_DATE", BILL_DATE)
                MyCommand.Parameters.AddWithValue("@BILL_AMT", BILL_AMT)
                MyCommand.Parameters.AddWithValue("@BILL_TAX_AMT", BILL_TAX_AMT)
                MyCommand.Parameters.AddWithValue("@BILL_DCPER", BILL_DCPER)
                MyCommand.Parameters.AddWithValue("@BILL_DCAMT", BILL_DCAMT)
                MyCommand.Parameters.AddWithValue("@BILL_SVC", BILL_SVC)
                MyCommand.Parameters.AddWithValue("@BILL_TOTAL", BILL_TOTAL)
                MyCommand.Parameters.AddWithValue("@BILL_EMPCODE", BILL_EMPCODE)
                MyCommand.Parameters.AddWithValue("@BILL_ORDERTYPE", BILL_ORDERTYPE)
                MyCommand.Parameters.AddWithValue("@ATTRIBUTE", ATTRIBUTE)
                MyCommand.Parameters.AddWithValue("@CREATEDUSER", CREATEDUSER)
                MyCommand.Parameters.AddWithValue("@TBL_CODE", TBL_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_TIP", BILL_TIP)
                MyCommand.Parameters.AddWithValue("@BILL_DEPOSIT", BILL_DEPOSIT)
                MyCommand.Parameters.AddWithValue("@PRO_CODE", PRO_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_GUEST", BILL_GUEST)
                MyCommand.Parameters.AddWithValue("@BILL_NOTES", BILL_NOTES)
                Dim myParameter As SqlParameter = New SqlParameter("@Result", SqlDbType.BigInt)
                myParameter.Direction = ParameterDirection.Output
                MyCommand.Parameters.Add(myParameter)
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
                Return Convert.ToInt64(MyCommand.Parameters(MyCommand.Parameters.Count - 1).Value)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Sub BillUpdate(ByVal CUST_CODE As String, _
                                    ByVal BILL_DATE As Date, _
                                    ByVal BILL_EMPCODE As String, _
                                    ByVal BILL_ORDERTYPE As Integer, _
                                    ByVal ATTRIBUTE As String, _
                                    ByVal UPDATEDUSER As String, _
                                    ByVal BILL_CODE As Int64, _
                                    ByVal BILL_TIP As Double, _
                                    ByVal PRO_CODE As String, _
                                    ByVal BILL_GUEST As String, _
                                    ByVal BILL_NOTES As String, _
                                    ByVal BILL_DEPOSIT As Double)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_UPDATE", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@CUST_CODE", CUST_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_DATE", BILL_DATE)
                MyCommand.Parameters.AddWithValue("@BILL_EMPCODE", BILL_EMPCODE)
                MyCommand.Parameters.AddWithValue("@BILL_ORDERTYPE", BILL_ORDERTYPE)
                MyCommand.Parameters.AddWithValue("@ATTRIBUTE", ATTRIBUTE)
                MyCommand.Parameters.AddWithValue("@UPDATEDUSER", UPDATEDUSER)
                MyCommand.Parameters.AddWithValue("@BILL_CODE", BILL_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_TIP", BILL_TIP)
                MyCommand.Parameters.AddWithValue("@BILL_DEPOSIT", BILL_DEPOSIT)
                MyCommand.Parameters.AddWithValue("@PRO_CODE", PRO_CODE)
                MyCommand.Parameters.AddWithValue("@BILL_GUEST", BILL_GUEST)
                MyCommand.Parameters.AddWithValue("@BILL_NOTES", BILL_NOTES)
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Function BillDetailAdd(ByVal BD_BILLCODE As Int64, _
                                        ByVal ITEM_CODE As String, _
                                        ByVal ITEM_NAME As String, _
                                        ByVal PRICE As Double, _
                                        ByVal QUANTITY As Integer, _
                                        ByVal DSC_PER As Double, _
                                        ByVal VAT_PER As Double, _
                                        ByVal KEEP_PACK As String, _
                                        ByVal MEMO As String, _
                                        ByVal ATTRIBUTE As String, _
                                        ByVal CREATEDUSER As String, _
                                        ByVal SUBITEM_CODE As String, _
                                        ByVal PARENT_CODE As String, _
                                        ByVal PRO_CODE As String, _
                                        ByVal EMP_CODE As String) As Integer
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BillDetail_Add", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BD_BILLCODE", BD_BILLCODE)
                MyCommand.Parameters.AddWithValue("@ITEM_CODE", ITEM_CODE)
                MyCommand.Parameters.AddWithValue("@ITEM_NAME", ITEM_NAME)
                MyCommand.Parameters.AddWithValue("@PRICE", PRICE)
                MyCommand.Parameters.AddWithValue("@QUANTITY", QUANTITY)
                MyCommand.Parameters.AddWithValue("@DSC_PER", DSC_PER)
                MyCommand.Parameters.AddWithValue("@VAT_PER", VAT_PER)
                MyCommand.Parameters.AddWithValue("@KEEP_PACK", KEEP_PACK)
                MyCommand.Parameters.AddWithValue("@MEMO", MEMO)
                MyCommand.Parameters.AddWithValue("@ATTRIBUTE", ATTRIBUTE)
                MyCommand.Parameters.AddWithValue("@CREATEDUSER", CREATEDUSER)
                If PARENT_CODE = "" Then
                    MyCommand.Parameters.AddWithValue("@PARENT_CODE", DBNull.Value)
                Else
                    MyCommand.Parameters.AddWithValue("@PARENT_CODE", PARENT_CODE)

                End If
                MyCommand.Parameters.AddWithValue("@SUBITEM_CODE", SUBITEM_CODE)
                MyCommand.Parameters.AddWithValue("@PRO_CODE", SUBITEM_CODE)
                MyCommand.Parameters.AddWithValue("@EMP_CODE", EMP_CODE)
                Dim myParameter As SqlParameter = New SqlParameter("@Result", SqlDbType.Int)
                myParameter.Direction = ParameterDirection.Output
                MyCommand.Parameters.Add(myParameter)
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
                Return Convert.ToInt32(MyCommand.Parameters(MyCommand.Parameters.Count - 1).Value)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Function BillDetailUpdate(ByVal BD_CODE As Integer, _
                                        ByVal ITEM_CODE As String, _
                                        ByVal ITEM_NAME As String, _
                                        ByVal PRICE As Double, _
                                        ByVal QUANTITY As Integer, _
                                        ByVal DSC_PER As Double, _
                                        ByVal VAT_PER As Double, _
                                        ByVal KEEP_PACK As String, _
                                        ByVal MEMO As String, _
                                        ByVal ATTRIBUTE As String, _
                                        ByVal UPDATEDUSER As String, _
                                        ByVal EMP_CODE As String, _
                                        ByVal TIME_OUT As String) As Integer
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BillDetail_Update", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@ITEM_CODE", ITEM_CODE)
                MyCommand.Parameters.AddWithValue("@ITEM_NAME", ITEM_NAME)
                MyCommand.Parameters.AddWithValue("@PRICE", PRICE)
                MyCommand.Parameters.AddWithValue("@QUANTITY", QUANTITY)
                MyCommand.Parameters.AddWithValue("@DSC_PER", DSC_PER)
                MyCommand.Parameters.AddWithValue("@VAT_PER", VAT_PER)
                MyCommand.Parameters.AddWithValue("@KEEP_PACK", KEEP_PACK)
                MyCommand.Parameters.AddWithValue("@MEMO", MEMO)
                MyCommand.Parameters.AddWithValue("@ATTRIBUTE", ATTRIBUTE)
                MyCommand.Parameters.AddWithValue("@UPDATEDUSER", UPDATEDUSER)
                MyCommand.Parameters.AddWithValue("@BD_CODE", BD_CODE)
                MyCommand.Parameters.AddWithValue("@EMP_CODE", EMP_CODE)
                If TIME_OUT = "" Then
                    MyCommand.Parameters.AddWithValue("@TIME_OUT", DBNull.Value)

                Else
                    MyCommand.Parameters.AddWithValue("@TIME_OUT", TIME_OUT)

                End If

                MyCommand.ExecuteNonQuery()

                PublicVariable.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Function Bill_Search(ByVal FromDate As Date, ByVal ToDate As Date) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_Search", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@FromDate", FromDate)
                MyCommand.Parameters.AddWithValue("@ToDate", ToDate)
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Function LoadBillDetail(ByVal BILL_CODE As Int64) As DataTable
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILLDETAIL_LOADBYBILL", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BILL_CODE", BILL_CODE)
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                Return mytable
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Sub BILL_CANCEL(ByVal BILL_CODE As Int64)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_CANCEL", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BILL_CODE", BILL_CODE)
                MyCommand.Parameters.AddWithValue("@USR_CODE", PublicVariable.UserCode)
                Dim ds As New DataSet
                Using da As New SqlDataAdapter()
                    ds.Clear()
                    da.SelectCommand = MyCommand
                    da.Fill(ds)
                End Using
                PublicVariable.Connection.Close()
                'BLL.ReturnXML("BILL", ds.Tables(0))
                'BLL.ReturnXML("BILL_DETAIL", ds.Tables(1))
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Sub BILLDETAIL_CANCEL(ByVal BD_CODE As Integer)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILLDETAIL_CANCEL", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BD_CODE", BD_CODE)
                MyCommand.Parameters.AddWithValue("@USR_CODE", PublicVariable.UserCode)
                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Function BILL_GETNUMBER() As String
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_GET_BILLNUMBER", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BILL_DATE", PublicVariable.SystemDate)
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()

                If mytable.Rows.Count > 0 Then
                    Return mytable.Rows(0).Item("BILL_NUMBER").ToString
                Else
                    Return "00001"
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Function
    Public Shared Sub UpdateDiscount(ByVal DISCOUNT As Double, ByVal BD_CODE As Integer)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILLDETAIL_DISCOUNT", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BD_CODE", BD_CODE)
                MyCommand.Parameters.AddWithValue("@DSC_PER", DISCOUNT)

                MyCommand.ExecuteNonQuery()
                PublicVariable.Connection.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Shared Sub BILL_REFUND(ByVal BILL_CODE As Int64)
        Try
            If PublicFunction.OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand("BILL_REFUND", PublicVariable.Connection)
                MyCommand.CommandType = CommandType.StoredProcedure
                MyCommand.Parameters.AddWithValue("@BILL_CODE", BILL_CODE)
                MyCommand.Parameters.AddWithValue("@USR_CODE", PublicVariable.UserCode)
                Dim ds As New DataSet
                Using da As New SqlDataAdapter()
                    ds.Clear()
                    da.SelectCommand = MyCommand
                    da.Fill(ds)
                End Using
                PublicVariable.Connection.Close()
                'BLL.ReturnXML("BILL", ds.Tables(0))
                'BLL.ReturnXML("BILL_DETAIL", ds.Tables(1))
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub


End Class
